from datetime import datetime

def save_college_to_db(data, connection):
    with connection.cursor() as cursor:
        # 1. 插入 universities 表（不传 id）
        sql_universities = """
            INSERT INTO universities (
                display_name, name, start_year, category, type,
                belong, belong_type, belong_type_text,
                owner_type, owner_type_text,
                logo_url, baike_url, enroll_url, yggk_url, vr_url,
                rank_order, version, update_time,
                province, city, city_level
            ) VALUES (
                %(displayName)s, %(name)s, %(startYear)s, %(category)s, %(type)s,
                %(belong)s, %(belongType)s, %(belongTypeText)s,
                %(ownerType)s, %(ownerTypeText)s,
                %(logoUrl)s, %(baikeUrl)s, %(enrollUrl)s, %(yggkUrl)s, %(vrUrl)s,
                %(rkOrder)s, %(version)s, %(updateTime)s,
                %(province)s, %(city)s, %(cityLevel)s
            )
            ON DUPLICATE KEY UPDATE
                display_name=VALUES(display_name),
                name=VALUES(name),
                start_year=VALUES(start_year),
                category=VALUES(category),
                type=VALUES(type),
                belong=VALUES(belong),
                belong_type=VALUES(belong_type),
                belong_type_text=VALUES(belong_type_text),
                owner_type=VALUES(owner_type),
                owner_type_text=VALUES(owner_type_text),
                logo_url=VALUES(logo_url),
                baike_url=VALUES(baike_url),
                enroll_url=VALUES(enroll_url),
                yggk_url=VALUES(yggk_url),
                vr_url=VALUES(vr_url),
                rank_order=VALUES(rank_order),
                version=VALUES(version),
                update_time=VALUES(update_time),
                province=VALUES(province),
                city=VALUES(city),
                city_level=VALUES(city_level);
        """
        cursor.execute(sql_universities, {
            "displayName": data.get("displayName"),
            "name": data.get("name"),
            "startYear": data.get("startYear"),
            "category": data.get("category"),
            "type": data.get("type"),
            "belong": data.get("belong"),
            "belongType": data.get("belongType"),
            "belongTypeText": data.get("belongTypeText"),
            "ownerType": data.get("ownerType"),
            "ownerTypeText": data.get("ownerTypeText"),
            "logoUrl": data.get("logoUrl"),
            "baikeUrl": data.get("baikeUrl"),
            "enrollUrl": data.get("enrollUrl"),
            "yggkUrl": data.get("yggkUrl"),
            "vrUrl": data.get("vrUrl"),
            "rkOrder": data.get("rkOrder"),
            "version": data.get("version", 1),
            "updateTime": datetime.strptime(data.get("updateTime", datetime.now().strftime("%Y-%m-%d %H:%M:%S")), "%Y-%m-%d %H:%M:%S"),
            "province": data.get("province"),
            "city": data.get("city"),
            "cityLevel": data.get("cityLevel")
        })

        university_id = cursor.lastrowid  # 获取自增 ID

        # 2. 插入 university_tags 表
        sql_tags = """
            INSERT INTO university_tags (
                university_id, is_211, is_985, is_shuangyiliu, is_shuanggao,
                is_qiangji, is_top, is_gongjian, is_featured
            ) VALUES (
                %(id)s, %(t211)s, %(t985)s, %(tshuangyiliu)s, %(tshuanggao)s,
                %(tqiangji)s, %(tzhongdian)s, %(tgongjian)s, %(ttese)s
            )
            ON DUPLICATE KEY UPDATE
                is_211=VALUES(is_211),
                is_985=VALUES(is_985),
                is_shuangyiliu=VALUES(is_shuangyiliu),
                is_shuanggao=VALUES(is_shuanggao),
                is_qiangji=VALUES(is_qiangji),
                is_top=VALUES(is_top),
                is_gongjian=VALUES(is_gongjian),
                is_featured=VALUES(is_featured);
        """
        cursor.execute(sql_tags, {
            "id": university_id,
            "t211": data.get("t211"),
            "t985": data.get("t985"),
            "tshuangyiliu": data.get("tshuangyiliu"),
            "tshuanggao": data.get("tshuanggao"),
            "tqiangji": data.get("tqiangji"),
            "tzhongdian": data.get("tzhongdian"),
            "tgongjian": data.get("tgongjian"),
            "ttese": data.get("ttese"),
        })

        # 3. 插入 university_stats 表
        byl = data.get("byl")
        try:
            byl_float = float(byl.strip('%')) if byl else None
        except Exception:
            byl_float = None

        sql_stats = """
            INSERT INTO university_stats (
                university_id, graduation_rate, sorted, master_count, doctor_count,
                core_count, special_count, dscpg_count
            ) VALUES (
                %(id)s, %(byl)s, %(sorted)s, %(masterCount)s, %(doctorCount)s,
                %(coreCount)s, %(ylCount)s, %(dscpgCount)s
            )
            ON DUPLICATE KEY UPDATE
                graduation_rate=VALUES(graduation_rate),
                sorted=VALUES(sorted),
                master_count=VALUES(master_count),
                doctor_count=VALUES(doctor_count),
                core_count=VALUES(core_count),
                special_count=VALUES(special_count),
                dscpg_count=VALUES(dscpg_count);
        """
        cursor.execute(sql_stats, {
            "id": university_id,
            "byl": byl_float,
            "sorted": data.get("sorted"),
            "masterCount": data.get("masterCount"),
            "doctorCount": data.get("doctorCount"),
            "coreCount": data.get("coreCount"),
            "ylCount": data.get("ylCount"),
            "dscpgCount": data.get("dscpgCount")
        })

        connection.commit()
        print(f"[✓] 已插入或更新大学：{data.get('displayName')}，ID：{university_id}")
